CREATE DATABASE MedicalStoreBilling
GO
USE MDSB
GO
Create table Customer
(
	cID int identity(1,1) Primary Key,
	cName varchar(50) not null,
	cType varchar(50) not null,
	cAddress varchar(50) not null,
	cPhone varchar(12) not null,
	cEmail varchar(50),
	cRelationship varchar(100)
)
GO
Create table Supplier
(
	[sID] int identity(1,1) Primary key,
	sName varchar(50) not null,
	sFullName varchar(100) not null,
	sPhone varchar(12) not null,
	sFax varchar(12),
	sType varchar(50),
	sAddress varchar(50) not null,
	sEmail varchar(50),
	sWebsite varchar(50)
)
GO
Create table Product
(
	pID int identity(1,1) primary key,
	pName varchar(50) not null,
	pType varchar(50) not null,
	pProductionDate Datetime not null, --ngay san xuat
	pExpiryDate Datetime not null, --han su dungs
	pImportDate datetime not null,
	pPrice float not null,
	pTax float not null, --thue
	pQuantity int not null,
	pSellPrice float not null,
	pDescription varchar(1000) not null
)
GO
Create table Purchase
(
	pcID int identity(1,1) Primary key,
	[sID] int not null Foreign key references Supplier([sID]),
	pPaid float not null,
	pUnpaid float not null,
	pcDate DateTime not null
)

GO
Create table PurchaseDetails
(
	pcdID int identity(1,1) Primary key,
	pcId int not null Foreign key references Purchase(pcID) On delete cascade,
	pID int not null Foreign key references Product(pID),
	pcdQuatity int not null,
	pcdPrice float not null,
	pcdDate datetime
)
GO

Create table Bill
(
	bID int identity(1,1) Primary key,
	cID int not null Foreign key references Customer(cID),
	bDeliveryDate Datetime not null,
	bDeliveryAddress varchar(50) not null,
	bDiscount float, -- khau tru phan tram
	bPaid float not null,
	bUnpaid float not null,
	bDate DateTime ,
	bStatus int default 0 -- this is Order. If status is 1 , it is will Bill
)

Go

Create table BillDetails
(
	bdID int identity(1,1) Primary key,
	bID int not null Foreign key references Bill(bID) On delete cascade,
	pID int not null Foreign key references Product(pID),
	bdPrice float not null,
	bdQuantity int not null,
	bdDate Datetime not null,
)